/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.struts;

import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import javax.security.auth.login.Configuration;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

/**
 *
 * @author Roberto
 */
public class UsuarioHelper {

    Session session = null;

    public UsuarioHelper() {
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
    }
    public List getUsers() {
        List<Usuario> userList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Usuario");
            userList = (List<Usuario>) q.list();
        } catch (Exception e) {
        }
        return userList;
    }
    
    // Obtener la lista de usuarios
    public List getTablas() {
        List userList = new LinkedList();
        try {
          
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Tablas");
            userList = (List<Usuario>) q.list();

        } catch (Exception e) {
        }
        return userList; 
    }
    
    /*
      public Fecha getFecha() {
        Fecha fecha = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Fecha");
            fecha = (Fecha) q.list();
        } catch (Exception e) {
        }
        return fecha;
    }
*/
    
    public boolean existeUsuario(String username) {
        boolean existe = false;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("FROM Usuario u WHERE u.usuario = '" + username + "'");
            if (!q.list().isEmpty()) {
                existe = true;
            }
        } catch (Exception e) {System.out.println(e);
        
        }
        return existe;
    }
    
    public List getUsersOrder(String tipo) {
        List<Usuario> userList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("FROM Usuario u ORDER BY u." + tipo + " ASC");
            userList = (List<Usuario>) q.list();
        } catch (Exception e) {System.out.println(e);
        }
        return userList;
    }
    
    public boolean loginAccess(String id, String password, List userList) {
        System.out.println(id + " " + password);
        Iterator i = userList.iterator();
        while (i.hasNext()) {
            Usuario u = (Usuario) i.next();
            if (u.getUsuario().equals(id)) {
                if (u.getPassword().equals(password)) {
                    return true;
                }
            }
        }
        return false;
    }
    
    public Usuario infoUser(String id) {
        List<Usuario> userList = getUsers();
        Iterator i = userList.iterator();
        while (i.hasNext()) {
            Usuario u = (Usuario) i.next();
            if (u.getUsuario().equals(id)) {
                return u;
            }
        }
        return null;
    }
    
    public void insertUser(String id, String password, String nombre, String correo, String categoria) {
        this.session.beginTransaction();
        Usuario user = new Usuario();
        user.setUsuario(id);
        user.setPassword(password);
        user.setNombre(nombre);
        user.setCorreo(correo);
        user.setCategoria(categoria);
        session.save(user);
        session.getTransaction().commit();
    }
    
    public void deleteUser(String id) {
        try {
            this.session.beginTransaction();
            Usuario user = (Usuario) session.load(Usuario.class,id);
            session.delete(user);
            session.getTransaction().commit();
        } catch (Exception e) {System.out.println(e);
        }
    }
    
	public void modUser(Usuario newuser) {
        try {
            this.session.beginTransaction();
            Usuario user = (Usuario) session.get(Usuario.class, newuser.getUsuario());
            user.setCorreo(newuser.getCorreo());
            user.setNombre(newuser.getNombre());
            user.setPassword(newuser.getPassword());
            user.setUsuario(newuser.getUsuario());
            user.setCategoria(newuser.getCategoria());
            //session.saveOrUpdate(newuser);
            session.getTransaction().commit();
        } catch (Exception e) {System.out.println(e);
        }
    }
	
    public List categoriaUsuarios(String categoria, List userList) {
        List<Usuario> lista = new LinkedList<Usuario>();
        Iterator i = userList.iterator();
        while (i.hasNext()) {
            Usuario u = (Usuario) i.next();
            if (u.getCategoria() != null && u.getCategoria().equals(categoria)) {
                //lista.add(u);
                System.out.println("Usuario: " + u.getUsuario());
                System.out.println("Correo: " + u.getCorreo());
                System.out.println("Nombre: " + u.getNombre());
                lista.add(u);
            }
        }
        return lista;
    }

    public void crearTabla(String nametable, List<String> parametros) {
            try {
                    String q = "CREATE TABLE " + nametable + " (";
        this.session.beginTransaction();
        Iterator i = parametros.iterator();
        int k = 0;
        while (i.hasNext()) {
            String p = (String) i.next();
            if (k != 0) {
                    q = q + ", " + p + " VARCHAR (30)";
            }else {
                q = q + p + " VARCHAR (30)";
                k++;
            }
        }
        q = q + ")";
        session.createSQLQuery(q).executeUpdate();
        session.getTransaction().commit();
    } catch (Exception e) {System.out.println(e);}
    }

    public void eliminarTabla(String nametable) {
    try {
         String q = "DROP TABLE " + nametable;
        this.session.beginTransaction();
        
         session.createSQLQuery(q).executeUpdate();
        session.getTransaction().commit();
    } catch (Exception e) {System.out.println(e);}
    }
    
    
    public void eliminarAtributo(String nametable,String atributo) {
    try {
         String q = "ALTER TABLE " + nametable +" DROP COLUMN " +atributo;
         this.session.beginTransaction(); 
        session.createSQLQuery(q).executeUpdate();
        session.getTransaction().commit();
    } catch (Exception e) {System.out.println(e);}
    }
    
    public void agregarAtributo(String nametable,String atributo) {
    try {
         String q = "ALTER TABLE " + nametable +" ADD COLUMN " +atributo+" character varying(30)";
        this.session.beginTransaction();
         session.createSQLQuery(q).executeUpdate();
        session.getTransaction().commit();
    } catch (Exception e) {System.out.println(e);}
    }
    
    public void insertTabla(String nombre, String categoria) {
        this.session.beginTransaction();
        Tablas tabla = new Tablas();
        tabla.setNombre(nombre);
        tabla.setCategoria(categoria);
        session.save(tabla);
        session.getTransaction().commit();
    }
    public List getAtributos(String tabla) {
        List userList = new LinkedList();
        try {
          
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery(" \\dt tabla");
            userList = (List) q.list();

        } catch (Exception e) {
        }
        return userList; 
    }
    
  //  \dt tabla
}